package com.gnizr.db.dao.comment;

import java.util.List;

import com.gnizr.db.dao.Bookmark;
import com.gnizr.db.dao.Comment;
import com.gnizr.db.dao.DaoResult;
import com.gnizr.db.dao.User;
import com.gnizr.db.vocab.CommentSchema;
import com.gnizr.db.dao.user.UserDBDao;
import com.gnizr.db.dao.bookmark.BookmarkDBDao;

import com.gnizr.db.dao.DBUtil;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;


import org.apache.log4j.Logger;
import javax.sql.DataSource;

public class CommentDBDao implements CommentDao {
	
	private DataSource dataSource;
	private static final Logger logger = Logger.getLogger(CommentDBDao.class.getName());
	private UserDBDao udb;
	public CommentDBDao(DataSource datasource){
		this.dataSource = datasource;
	}
	
	/**
	 * 
	 */
	private static final long serialVersionUID = -4984150946474083985L;

	public int createComment(Comment com) {
		logger.debug("createComment: comment= "+com);
		Connection conn = null;
		CallableStatement cStmt = null;
		int id = -1;
		try {
			conn = dataSource.getConnection();
			cStmt = conn.prepareCall("{call createComment(?,?,?,?,?)}");
			cStmt.setLong(1,com.getAuthor().getId());
			cStmt.setLong(2,com.getBookmarkID());
			cStmt.setString(3,com.getText());
			cStmt.setLong(4, com.getRank());
			cStmt.registerOutParameter(5,Types.INTEGER);
			cStmt.execute();
			id = cStmt.getInt(5);
		} catch (Exception e) {
			logger.fatal(e);
		} finally{
			try {
				DBUtil.cleanup(conn, cStmt);
			} catch (SQLException e) {
				logger.fatal(e);
			}
		}
		return id;
	}

	public boolean deleteComment(int id) {
		// TODO Auto-generated method stub
		return false;
	}

	public Comment getComment(int id) {
		logger.debug("input: id="+id);
		Comment cmt = null;
		PreparedStatement stmt = null;
		Connection conn = null;
		try{						
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement("call getComment(?);");
			stmt.setInt(1,id);
			ResultSet rs = stmt.executeQuery();
			if(rs.next()){
				cmt = createCommentObject2(rs);
				logger.debug("found: " + cmt);
			}else{
				logger.debug("found no matching comment");
			}
		}catch(Exception e){		
			logger.fatal(e);
		}finally{
			try {
				DBUtil.cleanup(conn,stmt);
			} catch (SQLException e) {
				logger.fatal(e);
			}
		}
		return cmt;
	}
	

	public int getCommentCount(Bookmark bookmark) {
		// TODO Auto-generated method stub
		return 0;
	}
	public static Comment createCommentObject(ResultSet rs) throws SQLException {
		if(rs == null) return null;
		Comment cmt = new Comment();
		cmt.setId(rs.getInt(CommentSchema.ID));
		cmt.setText(rs.getString(CommentSchema.TEXT));
		cmt.setCreatedOn(rs.getTimestamp(CommentSchema.CREATED_ON));
		cmt.setLastUpdated(rs.getTimestamp(CommentSchema.LAST_UPDATED));	
		cmt.setRank(rs.getInt(CommentSchema.RANK));
		
		User u = UserDBDao.createUserObject(rs);
		cmt.setAuthor(u);	
		
		return cmt;
	}
	public static Comment createCommentObject2(ResultSet rs) throws SQLException {
		if(rs == null) return null;
		Comment cmt = createCommentObject(rs);
		
		User u = UserDBDao.createUserObject(rs);
		cmt.setAuthor(u);
		
		return cmt;
	}

	public DaoResult<Comment> pageComments(Bookmark bookmark, int offset,
			int count) {
		logger.debug("pageComment, input: bookmark="+bookmark
				+",offset="+offset+",count="+count);
		Connection conn = null;
		CallableStatement stmt = null;
		DaoResult<Comment> result= null;
		List<Comment> cmnts = new ArrayList<Comment>();
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareCall("call getCommentsBm(?, ?)");
			stmt.setInt(1,bookmark.getId());
			stmt.registerOutParameter(2,Types.INTEGER);
			ResultSet rs = stmt.executeQuery();
			int size = stmt.getInt(2);
			if(size < 0){
				size = 0;
			}
			while(rs.next()){
				Comment c = createCommentObject(rs);
				logger.debug("found cmnt="+c);
				cmnts.add(c);
			}
			result = new DaoResult<Comment>(cmnts,size);
		} catch (SQLException e) {
			logger.fatal(e);
		}finally{
			try {
				DBUtil.cleanup(conn, stmt);
			} catch (SQLException e) {
				logger.fatal(e);
			}
		}		
		return result;
	}

	public DaoResult<Comment> pageComments(User user, int offset, int count) {
		logger.debug("pageComment, input: user="+user
				+",offset="+offset+",count="+count);
		Connection conn = null;
		CallableStatement stmt = null;
		DaoResult<Comment> result= null;
		List<Comment> cmnts = new ArrayList<Comment>();
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareCall("call getCommentsUser(?, ?)");
			stmt.setInt(1,user.getId());
			stmt.registerOutParameter(2,Types.INTEGER);
			ResultSet rs = stmt.executeQuery();
			int size = stmt.getInt(2);
			if(size < 0){
				size = 0;
			}
			while(rs.next()){
				Comment c = createCommentObject(rs);
				logger.debug("found cmnt="+c);
				cmnts.add(c);
			}
			result = new DaoResult<Comment>(cmnts,size);
		} catch (SQLException e) {
			logger.fatal(e);
		}finally{
			try {
				DBUtil.cleanup(conn, stmt);
			} catch (SQLException e) {
				logger.fatal(e);
			}
		}		
		return result;
	}

	public boolean updateComment(Comment com) {
		// TODO Auto-generated method stub
		return false;
	}


	public List<Comment> findComment(User user, Bookmark bm) {
		// TODO Auto-generated method stub
		return null;
	}

}
